/*------------------<-- Start of Description -->---------------------\
| Select variables names from a dataset, use for SELECT statement in |
| PROC SQL; |
|--------------------<-- End of Description -->----------------------|
|--------------------------------------------------------------------|
|-----------<-- Start of Files or Arguements Needed-->---------------|
| Arguments: |
| indata - the input dataset; |
| dalias - the alias of the input dataset; |
| var - the name of variables you want to select; |
| except - the number of variables you don't want to select; |
|------------<-- End of Files or Arguements Needed -->---------------|
|--------------------------------------------------------------------|
|------------------<-- Start of Files Created -->--------------------|
| Example 1: |
| 1) %let x=%selectvar(var=test xtest and stupid); |
| %let y=%selectvar(indata=attest.covar, except=atp THERA); |
| %put x = &x., y = &y..; |
| 2) |
| proc sql; |
| create table temp as |
| select %selectvar(indata=attest.monster, dalias=m), |
| %selectvar(indata=attest.covar, dalias=c, |
| except=atp MVD commnt1-commnt10) |
| from attest.covar as c, attest.monster as m; |
| quit; |
| Example 2: |
| proc sql; |
| select %selectvar(acedcrf.implant, a, pt serialno) |
| from acedcrf.implant as a; |
| quit; |
| Usage: %selectvar(indata=, dalias, var=, except=); |
\------------------<-- Start of Files Created -->-------------------*/
%macro selectvar(indata=, dalias=, var=, except=)/parmbuff;
/*--------------------------------------------\
| Author: Duo Zhou; |
| Created: 11-3-2001 10:52pm; |
| Modified: 5-9-2002 7:15pm; |
| Purpose: Select variable names from a data;|
\--------------------------------------------*/
%local nvars var vars numvars nargs argtmp argmnt1 argmnt2 argmnt3 argmnt4 currvar _indsid_ _invari_
_innvars_ _invtype_ _convar_ _invrc_ _excptnvars_ _conexcpt_ _excpti_ _excptvtype_;
%do i=1 %to 2;
%let argtmp=%qscan(%quote(&syspbuff),&i, %str(,()));
%if (not %index(%quote(%upcase(%sysfunc(compress(%quote(&argtmp))))),%quote(%upcase(=)))) %then %do;
%if &i eq 1 %then %let indata=&argtmp;
%else %if &i=2 %then %let dalias=&argtmp;
%end;
%end;
%if (%length(&var) gt 1) %then %do;
/* Add some code to change the format of var10-var20 to var10 var11 var12 ... var20*/
%if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&var))))), %str(%()) and
%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&var))))), %str(%()) lt 2) %then %do;
%let var=%substr(%quote(%trim(%left(&var))), 2, %eval(%length(%quote(%trim(%left(&var))))-2));
%let var=%sysfunc(translate(%quote(&var), ' ', ','));
%end;
%local _tmpvari_ _tmpivar_ _sub1var_ _tmp1var_ _tmp2var_ _vartmp_ _convar_ _locali_;
%if (%index(%quote(&var), %quote(,))) %then %let _vardlm_=%quote(,);
%else %let _vardlm_=%quote( );
%if (%sysfunc(index(%quote(&var),_ALL_))) %then %let var=*;
%else %if (%sysfunc(index(%quote(%upcase(&var)),_CHARACTER_))) %then %do;
%if (%sysfunc(exist(&indata))) %then %do;
%let _indsid_=%sysfunc(open(&indata)); %let _innvars_=%sysfunc(attrn(&_indsid_,NVARS));
%let _convar_=;
%do _invari_=1 %to &_innvars_;
%let _invtype_=%sysfunc(vartype(&_indsid_, &_invari_));
%if (not %index(%quote(%upcase(&_invtype_)),%quote(N))) %then %do;
%let _convar_= %trim(%left(&_convar_)) %sysfunc(varname(&_indsid_,&_invari_));
%end;
%end;
%let _invrc_=%sysfunc(close(&_indsid_)); %let var=&_convar_;
%end;
%end;
%else %if (%sysfunc(index(%quote(%upcase(&var)),_NUMERIC_))) %then %do;
%if (%sysfunc(exist(&indata))) %then %do;
%let _indsid_=%sysfunc(open(&indata)); %let _innvars_=%sysfunc(attrn(&_indsid_,NVARS));
%let _convar_=;
%do _invari_=1 %to &_innvars_;
%let _invtype_=%sysfunc(vartype(&_indsid_, &_invari_));
%if (%index(%quote(&_invtype_),%quote(N))) %then %do;
%let _convar_= %trim(%left(&_convar_)) %sysfunc(varname(&_indsid_,&_invari_));
%end;
%end;
%let _invrc_=%sysfunc(close(&_indsid_)); %let var=&_convar_;
%end;
%end;
%else %do;
%let _convar_=;
%do %while(%index(%quote(&var), %str(-)));
%let _sub1var_=%substr(%quote(&var), 1, %eval(%index(%quote(&var), %str(-))-1));
%let var=%substr(%quote(&var), %eval(%index(%quote(&var), %str(-))+1), %eval(%length(&var)-%index(%quote(&var), %str(-))));
%let _tmpvari_=0;
%do %while(%length(%nrbquote(%scan(%nrbquote(&_sub1var_), %eval(&_tmpvari_+1), %nrbquote(&_vardlm_)))));
%let _tmpvari_=%eval(&_tmpvari_+1);
%let _tmp1var_=%nrbquote(%qscan(%nrbquote(&_sub1var_), &_tmpvari_, %%nrbquote(&_vardlm_)));
%if (%length(%nrbquote(%scan(%nrbquote(&_sub1var_), %eval(&_tmpvari_+1), %nrbquote(&_vardlm_))))) %then %do;
%if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_))));
%else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_))));
%end;
%else %if ( %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_)))+1)) %then %do;
%let _arrvarbeg_=%substr(%quote(&_tmp1var_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_)))+1), %eval(%length(&_tmp1var_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_))))));
%let _arrvarbroot_=%substr(%quote(&_tmp1var_), 1, %sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_))));
%if (%quote(&var) ne) %then %do;
%let _tmp2var_=%nrbquote(%qscan(%nrbquote(&var), 1, %%nrbquote(&_vardlm_)));
%if (%sysfunc(rxmatch(%sysfunc(rxparse($d $s)),&_tmp2var_))) %then %do;
%let _arrvarend_=%substr(%quote(&_tmp2var_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2var_)))+1), %eval(%length(&_tmp2var_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2var_))))));
%let _arrvareroot_=%substr(%quote(&_tmp2var_), 1,%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2var_))));
%if (%quote(&_arrvarbroot_) eq %quote(&_arrvareroot_)) %then %do;
%do _locali_=&_arrvarbeg_ %to %eval(&_arrvarend_-1);
%if (%quote(&_convar_) eq) %then %let _convar_=%trim(%left(&_arrvarbroot_))%trim(%left(&_locali_));
%else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%left(&_arrvarbroot_))%trim(%left(&_locali_));
%end;
%end;
%else %do;
%if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_))));
%else %let _convar_=%trim(%quote(%left(&_convar_)))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_))));
%put ==> Alert! Variable name &_tmp1var_ and &_tmp2var_ do not have the same pattern!;
%end;
%end;
%else %do;
%if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_))));
%else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_))));
%put ==> Alert! Variable name &_tmp2var_ does not have a numeric suffix!;
%end;
%end;
%else %do;
%if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_))));
%else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_))));
%put ==> Alert! No variable names are provided after &_tmp1var_ -!;
%end;
%end;
%else %do;
%if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_))));
%else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_))));
%put ==> Alert! Variable name &_tmp1var_ does not have a numeric suffix!;
%end;
%end;
%end;
%let var=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&var))));
%end;
/* End of Add */
%let vars=;
%let nvars=1;
%let var1=%qscan(&var, &nvars, %str( ));
%if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do;
%let vars=&dalias..&var1;
%end;
%else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do;
%let vars=&indata..&var1;
%end;
%else %do;
%let vars=&var1;
%end;
%do %while(%length(&&var&nvars)>1);
%let nvars=%eval(&nvars+1);
%let var&nvars=%qscan(&var, &nvars, %str( ));
%if &&var&nvars ne %then %do;
%if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do;
%let vars=&vars, &dalias..&&var&nvars;
%end;
%else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do;
%let vars=&vars, &indata..&&var&nvars;
%end;
%else %do;
%let vars=&vars, &&var&nvars;
%end;
%end;
%end;
%let nvars=&nvars-1;
%end;
%else %do;
%if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&except))))), %str(%()) and
%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&except))))), %str(%()) lt 2) %then %do;
%let except=%substr(%quote(%trim(%left(&except))), 2, %eval(%length(%quote(%trim(%left(&except))))-2));
%let except=%sysfunc(translate(%quote(&except), ' ', ','));
%end;
/* Add some code to change the format of excpt10-excpt20 to excpt10 excpt11 excpt12 ... excpt20*/
%local _tmpexcpti_ _tmpiexcpt_ _sub1excpt_ _tmp1excpt_ _tmp2excpt_ _excpttmp_ _conexcpt_ _localj_;
%if (%index(%quote(&except), %quote(,))) %then %let _excptdlm_=%quote(,);
%else %let _excptdlm_=%quote( );
%if (%sysfunc(index(%quote(&except),_ALL_))) %then %let except=*;
%else %if (%sysfunc(index(%quote(%upcase(&except)),_CHARACTER_))) %then %do;
%if (%sysfunc(exist(&indata))) %then %do;
%let _indsid_=%sysfunc(open(&indata)); %let _excptnvars_=%sysfunc(attrn(&_indsid_,NVARS));
%let _conexcpt_=;
%do _excpti_=1 %to &_excptnvars_;
%let _excptvtype_=%sysfunc(vartype(&_indsid_, &_excpti_));
%if (not %index(%quote(%upcase(&_excptvtype_)),%quote(N))) %then %do;
%let _conexcpt_= %trim(%left(&_conexcpt_)) %sysfunc(varname(&_indsid_,&_excpti_));
%end;
%end;
%let _invrc_=%sysfunc(close(&_indsid_)); %let except=&_conexcpt_;
%end;
%end;
%else %if (%sysfunc(index(%quote(%upcase(&except)),_NUMERIC_))) %then %do;
%if (%sysfunc(exist(&indata))) %then %do;
%let _indsid_=%sysfunc(open(&indata)); %let _excptnvars_=%sysfunc(attrn(&_indsid_,NVARS));
%let _conexcpt_=;
%do _excpti_=1 %to &_excptnvars_;
%let _excptvtype_=%sysfunc(vartype(&_indsid_, &_excpti_));
%if (%index(%quote(%upcase(&_excptvtype_)),%quote(N))) %then %do;
%let _conexcpt_= %trim(%left(&_conexcpt_)) %sysfunc(varname(&_indsid_,&_excpti_));
%end;
%end;
%let _invrc_=%sysfunc(close(&_indsid_)); %let except=&_conexcpt_;
%end;
%end;
%else %do;
%let _conexcpt_=;
%do %while(%index(%quote(&except), %str(-)));
%let _sub1excpt_=%substr(%quote(&except), 1, %eval(%index(%quote(&except), %str(-))-1));
%let except=%substr(%quote(&except), %eval(%index(%quote(&except), %str(-))+1), %eval(%length(&except)-%index(%quote(&except), %str(-))));
%let _tmpexcpti_=0;
%do %while(%length(%nrbquote(%scan(%nrbquote(&_sub1excpt_), %eval(&_tmpexcpti_+1), %nrbquote(&_excptdlm_)))));
%let _tmpexcpti_=%eval(&_tmpexcpti_+1);
%let _tmp1excpt_=%nrbquote(%qscan(%nrbquote(&_sub1excpt_), &_tmpexcpti_, %%nrbquote(&_excptdlm_)));
%if (%length(%nrbquote(%scan(%nrbquote(&_sub1excpt_), %eval(&_tmpexcpti_+1), %nrbquote(&_excptdlm_))))) %then %do;
%if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_))));
%else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_))));
%end;
%else %if ( %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_)))+1)) %then %do;
%let _arrexcptbeg_=%substr(%quote(&_tmp1excpt_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_)))+1), %eval(%length(&_tmp1excpt_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_))))));
%let _arrexcptbroot_=%substr(%quote(&_tmp1excpt_), 1, %sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_))));
%if (%quote(&except) ne) %then %do;
%let _tmp2excpt_=%nrbquote(%qscan(%nrbquote(&except), 1, %%nrbquote(&_excptdlm_)));
%if (%sysfunc(rxmatch(%sysfunc(rxparse($d $s)),&_tmp2excpt_))) %then %do;
%let _arrexcptend_=%substr(%quote(&_tmp2excpt_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2excpt_)))+1), %eval(%length(&_tmp2excpt_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2excpt_))))));
%let _arrexcpteroot_=%substr(%quote(&_tmp2excpt_), 1,%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2excpt_))));
%if (%quote(&_arrexcptbroot_) eq %quote(&_arrexcpteroot_)) %then %do;
%do _localj_=&_arrexcptbeg_ %to %eval(&_arrexcptend_-1);
%if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%left(&_arrexcptbroot_))%trim(%left(&_localj_));
%else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%left(&_arrexcptbroot_))%trim(%left(&_localj_));
%end;
%end;
%else %do;
%if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_))));
%else %let _conexcpt_=%trim(%quote(%left(&_conexcpt_)))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_))));
%put ==> Alert! excptiable name &_tmp1excpt_ and &_tmp2excpt_ do not have the same pattern!;
%end;
%end;
%else %do;
%if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_))));
%else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_))));
%put ==> Alert! excptiable name &_tmp2excpt_ does not have a numeric suffix!;
%end;
%end;
%else %do;
%if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_))));
%else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_))));
%put ==> Alert! No excptiable names are provided after &_tmp1excpt_ -!;
%end;
%end;
%else %do;
%if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_))));
%else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_))));
%put ==> Alert! excptiable name &_tmp1excpt_ does not have a numeric suffix!;
%end;
%end;
%end;
%let except=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&except))));
%end;
/* End of Add */
%let dsid=%sysfunc(open(&indata));
%let vars=;
%if &dsid %then %do;
%let nvars=%sysfunc(attrn(&dsid,NVARS));
%let numvars=0;
%if &nvars>=1 %then %do;
%do _i_=1 %to &nvars;
%let var&_i_=%sysfunc(varname(&dsid,&_i_));
%if (%sysfunc(indexw(%upcase(&except),%upcase(&&var&_i_))) lt 1) %then %do;
%let numvars=%eval(&numvars+1);
%if &numvars = 1 %then %do;
%if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do;
%let vars=&dalias..&&var&_i_;
%end;
%else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do;
%let vars=&indata..&&var&_i_;
%end;
%else %do;
%let vars=&&var&_i_;
%end;
%end;
%else %do;
%if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do;
%let vars=&vars, &dalias..&&var&_i_.;
%end;
%else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do;
%let vars=&vars, &indata..&&var&_i_.;
%end;
%else %do;
%let vars=&vars, &&var&_i_;
%end;
%end;
%end;
%end;
%end;
%else %if &nvars<1 %then %do;
%put ==> Alert! Data "&indata" has no variables. ;
%end;
%let rc=%sysfunc(close(&dsid));
%end;
%else %do;
%put ==> Alert! Data "&indata" is invalid. ;
%end;
%end;&vars
%put;
%mend selectvar;
